package kr.hkit.summary_java.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.myframework.db.ConnectionFactory;
import kr.hkit.myframework.db.DbUtil;
import kr.hkit.summary_java.erp.dto.CodeTitle;

public class CodeTitleDAO implements DaoInterface<CodeTitle> {
	private Connection connection = ConnectionFactory.getConnection();
	
	@Override
	public void insertItem(CodeTitle item) throws SQLException {
		String sql = "insert into code_title values(?, ?)";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		pstmt.setString(2, item.getTitleName());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);	
	}

	@Override
	public void deleteItemByNo(CodeTitle item) throws SQLException {
		String sql = "delete from code_title where titleno = ?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	@Override
	public void updateItemByNo(CodeTitle item) throws SQLException {
		String sql = "update code_title set titlename=? where titleno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getTitleName());
		pstmt.setInt(2, item.getTitleNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	@Override
	public ArrayList<CodeTitle> listItems() throws SQLException {
		String sql = "select titleno, titlename from code_title";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		ArrayList<CodeTitle> list = new ArrayList<>();
		while(rs.next()){
			list.add(new CodeTitle(
					rs.getInt("titleno"), 
					rs.getString("titlename")));
		}
	
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	@Override
	public CodeTitle searchItem(CodeTitle item) throws SQLException {
		CodeTitle codeTitle = null;
		String sql = "select titleno, titlename from code_title where titleno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		ResultSet rs = pstmt.executeQuery();
		
		if (rs.next()) {
			codeTitle = new CodeTitle();
			codeTitle.setTitleNo(rs.getInt(1));
			codeTitle.setTitleName(rs.getString(2));
		}
		
		return codeTitle;
	}

}
